Excel Power Query抓取多个网页数据并配合Power Pivot进行分析 |
您所在的位置:网站首页 › power query常用函数 › Excel Power Query抓取多个网页数据并配合Power Pivot进行分析 |
本节内容使用Excel的Power Query和Power Pivot组件,抓取多个网页数据,进行清洗、建模和分析。 第一部分:从网页动态抓取数据 使用Power Query不仅可以获取本地的Excel文件数据,还可以获取网页数据。 本节介绍如何使用Power Query获取新浪网新浪体育频道的新浪直播室网页中的足球排行榜数据,主要获取列表中的全部赛季的球队数据,赛事主要获取前5项数据(前5项赛事的数据结构是相同的),如图所示。 网址: http://match.sports.sina.com.cn/football/opta_rank.php?year=2013&lid=1 默认打开网页时网址中有一部分为“year=2013&lid=1”;当“选择日期”为“2015赛季”、“选择赛事”为“德甲”时,网址中的这部分变为“year=2015&lid=3”。 对比网址中的这两部分不难发现,其中“year=2013”和“year=2015”部分表示赛事日期,而“lid=1”和“lid=3”部分分别表示赛事的名称,对应赛事列表中的赛事名称,而其他部分完全不变。 通过以上的发现,就可以将这两个参数构造为变量,创建自定义函数来依次获取网页中的数据。具体的操作步骤如下所述。 第1步:复制目标网页中的网址。首先新建一个Excel工作簿,将其打开后依次选择“数据”→“获取数据”→“来自其他源”→“自网站”选项,然后在弹出的“从Web”对话框中选中“高级”单选按钮,接着将网址按参数进行拆分,并分别填写至“URL部分”区域的各个对应的文本框中,最后单击“确定”按钮,如图6-15所示。 第2步:在弹出的“导航器”对话框的左侧选择“Table 0”选项,就可以在右侧看到当前网址对应的表格数据,然后单击“转换数据”按钮,如图6-16所示。 第3步:在Power Query中创建自定义函数。选择“Table 0”查询,打开“高级编辑器”窗口,将公式修改为自定义函数,自定义函数的名称为“Sdata”,如图所示。 (x as number, y as number) => let 源 = Web.Page( Web.Contents( "http://match.sports.sina.com.cn/football/opta_rank.php?year=" & Text.From(x) & "&lid=" & Text.From(y) ) ), Data0 = 源{0}[Data] in Data0第4步:创建网址中的两个参数的列表。新建一个空查询,直接使用公式生成以下的表。也可以提前在Excel表中准备好,直接导入即可使用,如图6-18所示。 = Table.ExpandTableColumn( Table.AddColumn( Table.FromList({2011 .. 2021}, each {_}, {"赛季"}), "赛事代码和赛事名称", each #table({"赛事代码", "赛事名称"}, { {1, "英超"}, {2, "西甲"}, {3, "德甲"}, {4, "意甲"}, {5, "法甲"} }) ), "赛事代码和赛事名称", {"赛事代码", "赛事名称"} )第5步:在第4步创建好的表中直接调用自定义函数。首先单击“添加列”→“调用自定义函数”按钮,然后在弹出的“调用自定义函数”对话框的“新列名”文本框中输入“Sdata”,在“功能查询”下拉列表中选择自定义的函数“Sdata”,在“x”下拉列表中选择“赛季”选项,在“y”下拉列表中选择“赛事代码”选项,最后单击“确定”按钮,如图所示。 第6步:单击“Sdata”列中的“Table”元素可以预览获取的数据。将“Sdata”列展开后,将结果上载至Excel工作表和数据模型中,如图所示。 需要注意的是,虽然Excel中的Power Query可以获取一些常规的比较简单的网页数据,但是其能力毕竟有限,对于复杂的数据的获取就无能为力了。 第二部分:数据分析 抓取的数据是一个单表,再没有其他的表,此处再不用建立其他的维度表。 本期我们使用Excel Power Pivot进行分析,打造一个自定义表头的数据透视表,并且可以使用切片器进行切片。结果如下图所示。 具体的操作步骤如下。 第1步:在Excel工作表中建立一个标题行的数据表,并添加到数据模型中,表名为“标题”,该表与已经抓取的数据表不用建立任何关系。对“一级标题名称”执行"按列排序"操作,依据为"一级标题序号"列,对“二级标题名称”执行"按列排序"操作,依据为"二级标题序号"列。如图所示。 第2步:分别编写上述9个度量值。具体如下。(向左划动可查看完整代码) 总胜场 := SUM ( '足球数据'[胜] )---------------------------------------总负场 := SUM ( '足球数据'[负] )---------------------------------------总进球 := SUM ( '足球数据'[进球] )---------------------------------------前3名的总净胜球 :=CALCULATE ( SUM ( '足球数据'[净胜球] ), '足球数据'[排名] |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |